﻿using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using MySql.Data.MySqlClient;
using EShop.Common;
using EShop.BussinessObjects;

namespace EShop.DataAccess
{
    public class CDAContact
    {
            public static Boolean Delete(Contact contact, out string errMsg)
            {
                MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
                con.Open();
                errMsg = string.Empty;
                string _cmdText = " DELETE contact " +
                                  " WHERE ID = " + contact.ID.ToString();
                try
                {
                    MySqlCommand cmd = new MySqlCommand(_cmdText, con);
                    cmd.ExecuteNonQuery();

                    con.Close();
                    return true;
                }
                catch (Exception e)
                {
                    errMsg = e.Message;
                con.Close();
                return false;
            }
        }

        public static Boolean Update(Contact contact, out string errMsg)
        {
            MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
            con.Open();
            errMsg = string.Empty;
            string _cmdText = " UPDATE contact SET " +
                              "  FirstName = " + ((contact.FirstName == null) ? "NULL" : "'" + contact.FirstName.Trim() + "' ") +
                              " ,LastName = " + ((contact.LastName == null) ? "NULL" : "'" + contact.LastName.Trim() + "' ") +
                              " ,EmailAddress = " + ((contact.EmailAddress == null) ? "NULL" : "'" + contact.EmailAddress.Trim() + "' ") +
                              " ,TelNumber = " + ((contact.TelNumber == null) ? "NULL" : "'" + contact.TelNumber.Trim() + "' ") +
                              " ,CellNumber = " + ((contact.CellNumber == null) ? "NULL" : "'" + contact.CellNumber.Trim() + "' ") +
                              " ,FaxNumber = " + ((contact.FaxNumber == null) ? "NULL" : "'" + contact.FaxNumber.Trim() + "' ") +
                              " ,CountryID = " + ((string.IsNullOrEmpty(contact.CountryID.ToString()) || (contact.CountryID == (int)cnstDatabase.DBOperationResult.NullValue)) ? "NULL" : contact.CountryID.ToString()) +
                              " ,StateID = " + ((string.IsNullOrEmpty(contact.StateID.ToString()) || (contact.StateID == (int)cnstDatabase.DBOperationResult.NullValue)) ? "NULL" : contact.StateID.ToString()) +
                              " ,PostalCode = " + ((contact.PostalCode == null) ? "NULL" : "'" + contact.PostalCode.Trim() + "' ") +
                              " ,Street = " + ((contact.Street == null) ? "NULL" : "'" + contact.Street.Trim() + "' ") +
                              " ,HomeNumber = " + ((contact.HomeNumber == null) ? "NULL" : "'" + contact.HomeNumber.Trim() + "' ") +
                              " ,Stair = " + ((contact.Stair == null) ? "NULL" : "'" + contact.Stair.Trim() + "' ") +
                              " ,Floor = " + ((contact.Floor == null) ? "NULL" : "'" + contact.Floor.Trim() + "' ") +
                              " ,Door = " + ((contact.Door == null) ? "NULL" : "'" + contact.Door.Trim() + "' ") +
                              " ,IsConfirmedCustomer = " + ((contact.IsConfirmedCustomer == false) ? "0" : "1 ") +
                              " WHERE ID = " + contact.ID.ToString();
            try
            {
                MySqlCommand cmd = new MySqlCommand(_cmdText, con);
                cmd.ExecuteNonQuery();

                con.Close();
                return true;
            }
            catch (Exception e)
            {
                errMsg = e.Message;
                con.Close();
                return false;
            }
        }

        public static Boolean Insert(Contact contact, out string errMsg)
        {
            MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
            con.Open();
            errMsg = string.Empty;
            string _cmdText = " INSERT INTO contact (FirstName, LastName, EmailAddress, TelNumber, CellNumber, FaxNumber, CountryID, StateID, PostalCode, Street, HomeNumber, IsConfirmedCustomer, Stair, Floor, Door) " +
                              " VALUES(" + ((contact.FirstName == null) ? "NULL" : "'" + contact.FirstName.Trim() + "' ") +
                              " ," + ((contact.LastName == null) ? "NULL" : "'" + contact.LastName.Trim() + "' ") +
                              " ," + ((contact.EmailAddress == null) ? "NULL" : "'" + contact.EmailAddress.Trim() + "' ") +
                              " ," + ((contact.TelNumber == null) ? "NULL" : "'" + contact.TelNumber.Trim() + "' ") +
                              " ," + ((contact.CellNumber == null) ? "NULL" : "'" + contact.CellNumber.Trim() + "' ") +
                              " ," + ((contact.FaxNumber == null) ? "NULL" : "'" + contact.FaxNumber.Trim() + "' ") +
                              " ," + ((string.IsNullOrEmpty(contact.CountryID.ToString()) || (contact.CountryID == (int)cnstDatabase.DBOperationResult.NullValue)) ? "NULL" : contact.CountryID.ToString()) +
                              " ," + ((string.IsNullOrEmpty(contact.StateID.ToString()) || (contact.StateID == (int)cnstDatabase.DBOperationResult.NullValue)) ? "NULL" : contact.StateID.ToString()) +
                              " ," + ((contact.PostalCode == null) ? "NULL" : "'" + contact.PostalCode.Trim() + "' ") +
                              " ," + ((contact.Street == null) ? "NULL" : "'" + contact.Street.Trim() + "' ") +
                              " ," + ((contact.HomeNumber == null) ? "NULL" : "'" + contact.HomeNumber.Trim() + "' ") +
                              " ," + ((contact.IsConfirmedCustomer == false) ? "0" : "1 ") +
                              " ," + ((contact.Stair == null) ? "NULL" : "'" + contact.Stair.Trim() + "' ") +
                              " ," + ((contact.Floor == null) ? "NULL" : "'" + contact.Floor.Trim() + "' ") +
                              " ," + ((contact.Door == null) ? "NULL" : "'" + contact.Door.Trim() + "' ") +
                              ")";
            try
            {
                MySqlCommand cmd = new MySqlCommand(_cmdText, con);
                cmd.ExecuteNonQuery();

                _cmdText = " SELECT last_insert_id(); ";
                cmd = new MySqlCommand(_cmdText, con);
                int _id = Convert.ToInt32(cmd.ExecuteScalar());
                contact.ID = _id;

                con.Close();
                return true;
            }
            catch (Exception e)
            {
                errMsg = e.Message;
                con.Close();
                return false;
            }
        }

        public static List<Contact> Select(Contact contact)
        {
            MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
            con.Open();

            string _cmdText =
            " SELECT c.* " +
            " FROM contact c " +
            " WHERE ((c.ID = " + contact.ID.ToString() + ") OR (" + contact.ID.ToString() + " = " + ((int)cnstDatabase.DBOperationResult.ValueIsNotEffective).ToString() + ")) " +
            "ORDER BY c.ID";

            MySqlCommand cmd = new MySqlCommand(_cmdText, con);
            MySqlDataAdapter dataAdapter = new MySqlDataAdapter(cmd);
            DataTable tbl = new DataTable();
            dataAdapter.Fill(tbl);

            List<Contact> _contacts = new List<Contact>();

            Contact _contact;
            foreach (DataRow row in tbl.Rows)
            {
                _contact = new Contact();

                _contact.ID = (row["ID"].ToString() == "" ? ((int)cnstDatabase.DBOperationResult.NullValue) : Convert.ToInt32(row["ID"]));
                _contact.FirstName = (row["FirstName"].ToString() == "" ? null : Convert.ToString(row["FirstName"]));
                _contact.LastName = (row["LastName"].ToString() == "" ? null : Convert.ToString(row["LastName"]));
                _contact.EmailAddress = (row["EmailAddress"].ToString() == "" ? null : Convert.ToString(row["EmailAddress"]));
                _contact.TelNumber = (row["TelNumber"].ToString() == "" ? null : Convert.ToString(row["TelNumber"]));
                _contact.CellNumber = (row["CellNumber"].ToString() == "" ? null : Convert.ToString(row["CellNumber"]));
                _contact.FaxNumber = (row["FaxNumber"].ToString() == "" ? null : Convert.ToString(row["FaxNumber"]));
                _contact.CountryID = (row["CountryID"].ToString() == "" ? ((int)cnstDatabase.DBOperationResult.NullValue) : Convert.ToInt32(row["CountryID"]));
                _contact.StateID = (row["StateID"].ToString() == "" ? ((int)cnstDatabase.DBOperationResult.NullValue) : Convert.ToInt32(row["StateID"]));
                _contact.PostalCode = (row["PostalCode"].ToString() == "" ? null : Convert.ToString(row["PostalCode"]));
                _contact.Street = (row["Street"].ToString() == "" ? null : Convert.ToString(row["Street"]));
                _contact.HomeNumber = (row["HomeNumber"].ToString() == "" ? null : Convert.ToString(row["HomeNumber"]));
                _contact.IsConfirmedCustomer = (row["IsConfirmedCustomer"].ToString() == "1" ? true : false);
                _contact.Stair = (row["Stair"].ToString() == "" ? null : Convert.ToString(row["Stair"]));
                _contact.Floor = (row["Floor"].ToString() == "" ? null : Convert.ToString(row["Floor"]));
                _contact.Door = (row["Door"].ToString() == "" ? null : Convert.ToString(row["Door"]));

                _contacts.Add(_contact);
            }

            con.Close();
            return _contacts;
        }

        public static Contact SelectByNumber(string number)
        {
            MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
            con.Open();
            number = number.Trim().TrimStart('+','0');
            string _cmdText =
            " SELECT c.* " +
            " FROM contact c " +
            " WHERE ((c.TelNumber like '%" + number + "') OR (c.CellNumber like '%" + number + "') OR (c.FaxNumber like '%" + number + "')) " +
            " ORDER BY c.ID LIMIT 1";

            MySqlCommand cmd = new MySqlCommand(_cmdText, con);
            MySqlDataAdapter dataAdapter = new MySqlDataAdapter(cmd);
            DataTable tbl = new DataTable();
            dataAdapter.Fill(tbl);

            Contact _contact = new Contact();
            if (tbl.Rows.Count > 0)
            {
                DataRow row = tbl.Rows[0];
                
                _contact.ID = (row["ID"].ToString() == ""? ((int) cnstDatabase.DBOperationResult.NullValue): Convert.ToInt32(row["ID"]));
                _contact.FirstName = (row["FirstName"].ToString() == "" ? null : Convert.ToString(row["FirstName"]));
                _contact.LastName = (row["LastName"].ToString() == "" ? null : Convert.ToString(row["LastName"]));
                _contact.EmailAddress = (row["EmailAddress"].ToString() == ""? null: Convert.ToString(row["EmailAddress"]));
                _contact.TelNumber = (row["TelNumber"].ToString() == "" ? null : Convert.ToString(row["TelNumber"]));
                _contact.CellNumber = (row["CellNumber"].ToString() == "" ? null : Convert.ToString(row["CellNumber"]));
                _contact.FaxNumber = (row["FaxNumber"].ToString() == "" ? null : Convert.ToString(row["FaxNumber"]));
                _contact.CountryID = (row["CountryID"].ToString() == "" ? ((int) cnstDatabase.DBOperationResult.NullValue) : Convert.ToInt32(row["CountryID"]));
                _contact.StateID = (row["StateID"].ToString() == "" ? ((int) cnstDatabase.DBOperationResult.NullValue) : Convert.ToInt32(row["StateID"]));
                _contact.PostalCode = (row["PostalCode"].ToString() == "" ? null : Convert.ToString(row["PostalCode"]));
                _contact.Street = (row["Street"].ToString() == "" ? null : Convert.ToString(row["Street"]));
                _contact.HomeNumber = (row["HomeNumber"].ToString() == "" ? null : Convert.ToString(row["HomeNumber"]));
                _contact.IsConfirmedCustomer = (row["IsConfirmedCustomer"].ToString() == "1" ? true : false);
                _contact.Stair = (row["Stair"].ToString() == "" ? null : Convert.ToString(row["Stair"]));
                _contact.Floor = (row["Floor"].ToString() == "" ? null : Convert.ToString(row["Floor"]));
                _contact.Door = (row["Door"].ToString() == "" ? null : Convert.ToString(row["Door"]));

            }
            con.Close();
            return _contact;
        }
    }
}